/**
  * AntRanking Copyright (c) 2008-2012 INRIA, University of Lille 1
  *
  * This library is free software; you can redistribute it and/or
  * modify it under the terms of the GNU Lesser General Public
  * License as published by the Free Software Foundation; either
  * version 2 of the License, or (at your option) any later version.
  *
  * This library is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  * Lesser General Public License for more details.
  *
  * You should have received a copy of the GNU Lesser General Public
  * License along with this library; if not, write to the Free Software
  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  * 
  * Contact: 
  * 	benjamin.bertein@gmail.com,
  * 	nacim.hamdad@gmail.com
  * 	nicolas.haderer@inria.fr
  *
  * Author: Nacim Hamdad,Benjamin Bertein, Haderer Nicolas
  */
package com.google.antranking.database;

import com.google.antranking.utils.Log;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TrackDatabase {

	final static public String DATABASE_NAME = "AtTrackDB";
	final static public String DATABASE_ATT_ID = "_id";
	final static public String DATABASE_ATT_NAME = "_name";
	final static public String DATABASE_ATT_TRACK = "_track";
	
	
	/**
	 * Day where the track has been saved
	 * Format : YYYY-MM-DD
	 */
	final static public String DATABASE_ATT_DAY = "_day";

	private SQLiteDatabase db;
	private SQLiteOpenHelper helper;

	public TrackDatabase(Context context){
		this.helper = new TrackMeDBHelper(context);
	}


	/**
	 * Create or open database
	 */
	public void open(){
		this.db = this.helper.getWritableDatabase();
	}


	/**
	 * Close database
	 */
	public void close(){
		this.db.close();
	}


	/**
	 * Insert Track Point into database name
	 * @param name name of database
	 * @param track data to be inserted into database
	 * @param day Day where the track is insered under format "YYYY-MM-DD"
	 */
	public void insertTrack(final String name, final String track, final String day){
		
		final StringBuffer buf = new StringBuffer();
		buf.append("INSERT INTO "+TrackDatabase.DATABASE_NAME+" (_name, _track, _day) values ( ");
		buf.append("\""+name+"\"");
		buf.append(", ");
		buf.append("\""+this.parseTrack(track)+"\"");
		buf.append(", ");
		buf.append("\""+day+"\"");
		buf.append(");");

		db.execSQL(buf.toString());
	}

	
	public void updateTrackHeader(final String name, final String header){
		
		if (this.getTrackHeader(name) == null){
			
			// First header insert
			Log.d( "insert track header");
			final StringBuffer buf = new StringBuffer();
			buf.append("INSERT INTO AtTrackDBHeader (_name, _header) values ( ");
			buf.append("\""+name+"\"");
			buf.append(", ");
			buf.append("\""+this.parseTrack(header)+"\"");
			buf.append(");");
			db.execSQL(buf.toString());
			
		}
		else{
			//Header update
			Log.d("update track header");
			final StringBuffer buf = new StringBuffer();
			buf.append("UPDATE AtTrackDBHeader SET _header = ");
			buf.append("'"+header+"'");
			buf.append(";");
			db.execSQL(buf.toString());
		}
		
	}
	
	
	public String getTrackHeader(final String name){
		
		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(" _header ");
		query.append(" FROM ");
		query.append(" AtTrackDBHeader ");
		query.append(" WHERE ");
		query.append(TrackDatabase.DATABASE_ATT_NAME+" == \""+name+"\" ");
		
		

		final Cursor  cursor = db.rawQuery(query.toString(), null);
		final String[] result = new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			result[i] = cursor.getString(cursor.getColumnIndex("_header"));
			i++;
		}
		cursor.close();

		if (result.length == 0) return null;
		
		return result[0];
		
	}

	
	/**
	 * Remove all track of database with name xp
	 * @param xp
	 */
	public void clearDatabase(String xpName,String day){
		final StringBuffer query = new StringBuffer();
		query.append("DELETE  FROM "+DATABASE_NAME+" WHERE ( ");
		query.append("_name = '"+xpName+"'");
		query.append(" AND ");
		query.append("_day = '"+day+"'");
		query.append(" );");
		db.execSQL(query.toString());
	}
	
	/**
	 * Remove all tracks in database
	 */
	public void clearDatabase(){
		db.delete(TrackDatabase.DATABASE_NAME, "", null);
	}


	/**
	 * Return the name of all Tracks Experience of the databases
	 * @return the name of all Tracks Experience of the databases
	 */
	public String[] getExperiencesNames(){

		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(TrackDatabase.DATABASE_ATT_NAME);
		query.append(" FROM ");
		query.append(TrackDatabase.DATABASE_NAME);
		query.append(" GROUP BY ");
		query.append(TrackDatabase.DATABASE_ATT_NAME);
		query.append(";");

		final Cursor cursor = db.rawQuery(query.toString(), null);

		final String[] result = new String[cursor.getCount()];
		int i = 0;
		while(cursor.moveToNext()){
			result[i] = cursor.getString(cursor.getColumnIndex(TrackDatabase.DATABASE_ATT_NAME));
			i++;
		}
		cursor.close();

		return result;
	}


	/**
	 * 
	 * @param xpName
	 * @return
	 */
	public String[] getDatesForTrack(final String xpName){
		
		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(TrackDatabase.DATABASE_ATT_DAY);
		query.append(" FROM ");
		query.append(TrackDatabase.DATABASE_NAME);
		query.append(" WHERE ");
		query.append(TrackDatabase.DATABASE_ATT_NAME+" == \""+xpName+"\" ");
		query.append("GROUP BY ");
		query.append(TrackDatabase.DATABASE_ATT_DAY);
		

		final Cursor  cursor = db.rawQuery(query.toString(), null);
		final String[] result = new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			result[i] = cursor.getString(cursor.getColumnIndex(TrackDatabase.DATABASE_ATT_DAY));
			i++;
		}
		cursor.close();

		return result;
	}
	
	/**
	 * Return an Array of json track point of the experience name "xpName"
	 * @param xpName name of experience
	 * @return
	 */
	public String[] getTracks(final String xpName, final String day, final int minindex, final int maxindex){
		
		Log.d("query : get All tracks for xp : "+xpName+" day : "+day);

		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(TrackDatabase.DATABASE_ATT_TRACK);
		query.append(" FROM ");
		query.append(TrackDatabase.DATABASE_NAME);
		query.append(" WHERE ");
		query.append(TrackDatabase.DATABASE_ATT_NAME+" == \""+xpName+"\"");
		query.append(" AND ");
		query.append(TrackDatabase.DATABASE_ATT_DAY+" == \""+day+"\" LIMIT "+minindex+", "+maxindex);
		
		
		

		final Cursor  cursor = db.rawQuery(query.toString(), null);
		
		final String[] result = new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			result[i] = cursor.getString(cursor.getColumnIndex(TrackDatabase.DATABASE_ATT_TRACK));
			i++;
		}
		cursor.close();
		Log.d("query result "+result.length);
		
		
		return result;

	}
	
	
	public int getTrackNumber(final String xpName){
		
		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("COUNT("+TrackDatabase.DATABASE_ATT_TRACK+") ");
		query.append(" FROM ");
		query.append(TrackDatabase.DATABASE_NAME);
		query.append(" WHERE ");
		query.append(TrackDatabase.DATABASE_ATT_NAME+" == \""+xpName+"\"");
		
		
		final Cursor cursor = db.rawQuery(query.toString(), null);
		if (cursor.moveToNext())
			return cursor.getInt(cursor.getColumnIndex(cursor.getColumnNames()[0]));
		cursor.close();
		return 0;
	}
	

	/**
	 * Return an Array of json track point of the experience name "xpName"
	 * @param xpName name of experience
	 * @return
	 */
	public String[] getTracks(final String xpName, final String day){
		
		Log.d("query : get All tracks for xp : "+xpName+" day : "+day);

		final StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append(TrackDatabase.DATABASE_ATT_TRACK);
		query.append(" FROM ");
		query.append(TrackDatabase.DATABASE_NAME);
		query.append(" WHERE ");
		query.append(TrackDatabase.DATABASE_ATT_NAME+" == \""+xpName+"\"");
		query.append(" AND ");
		query.append(TrackDatabase.DATABASE_ATT_DAY+" == \""+day+"\"");
		
		
		

		final Cursor  cursor = db.rawQuery(query.toString(), null);
		final String[] result = new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			result[i] = cursor.getString(cursor.getColumnIndex(TrackDatabase.DATABASE_ATT_TRACK));
			i++;
		}
		cursor.close();
		
		Log.d("query result "+result.length);
		
		
		return result;

	}

	
	
	public SQLiteDatabase getSQLite(){
		return this.db;
	}

	private String parseTrack(final String track){
		return track.replaceAll("\"","'");
	}


	private static class TrackMeDBHelper extends SQLiteOpenHelper 
	{

		public TrackMeDBHelper(Context context) {
			super(context, TrackDatabase.DATABASE_NAME, null, 2);

		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{
			
			// Create table where tracks will be saved
			StringBuffer query = new StringBuffer();
			query.append("create table ");
			query.append(TrackDatabase.DATABASE_NAME);
			query.append(" ( ");
			query.append(" "+TrackDatabase.DATABASE_ATT_ID   +" integer primary key autoincrement, ");
			query.append(" "+TrackDatabase.DATABASE_ATT_NAME +" text not null, ");
			query.append(" "+TrackDatabase.DATABASE_ATT_TRACK+" text not null, ");
			query.append(" "+TrackDatabase.DATABASE_ATT_DAY+" text not null");
			query.append(" ); ");
			db.execSQL(query.toString());
			
			
			//create table for track header
			query = new StringBuffer();
			query.append("create table ");
			query.append(TrackDatabase.DATABASE_NAME+"Header");
			query.append(" ( ");
			query.append(" "+TrackDatabase.DATABASE_ATT_ID   +" integer primary key autoincrement, ");
			query.append(" "+TrackDatabase.DATABASE_ATT_NAME +" text not null, ");
			query.append(" _header text not null ");
			query.append(" ); ");
			db.execSQL(query.toString());

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, 
				int newVersion){
			
			//create table for track header
			final StringBuffer query = new StringBuffer();
			query.append("create table ");
			query.append(TrackDatabase.DATABASE_NAME+"Header");
			query.append(" ( ");
			query.append(" "+TrackDatabase.DATABASE_ATT_ID   +" integer primary key autoincrement, ");
			query.append(" "+TrackDatabase.DATABASE_ATT_NAME +" text not null, ");
			query.append(" _header text not null ");
			query.append(" ); ");
			db.execSQL(query.toString());
		}
	}    
}
